
* +++++++++++++++++++++
* CLEAN URBANITY DATA
* +++++++++++++++++++++

* fraction of houses that are designed for one or two familes only
* (Ein- und Zweifamilienhäuser)
import excel "${data_raw}/urban_rural/Ein- und Zweifamilienhäuser_Kreise_2017.xlsx", ///
	clear firstrow

ren (Kennziffer name EinundZweifamilienhäuser) (ags place_name frac_1or2famhouse)
	
keep ags place_name frac_1or2famhouse
destring ags, replace

tempfile frac_1or2famhouse
save `frac_1or2famhouse'

* average living space (Wohnfläche) per person
import excel "${data_raw}/urban_rural/Wohnfläche_Kreise_2017.xlsx", clear firstrow

ren (Kennziffer name Wohnfläche) (ags place_name living_space_pc)
	
keep ags place_name living_space_pc
destring ags, replace	

tempfile living_space_pc
save `living_space_pc'

* percent of flats that are empty
import excel "${data_raw}/urban_rural/Wohnungsleerstand_Kreise_2011.xlsx", clear firstrow

ren (Kennziffer name Wohnungsleerstand) (ags place_name pct_empty_flats)
keep ags place_name pct_empty_flats
destring ags, replace		
	
* crosswalk to new Kreis definitions
replace ags = 13071 if inlist(ags,13002, 13056, 13055, 13052)
replace ags = 13072 if inlist(ags,13051, 13053)
replace ags = 13073 if inlist(ags,13005, 13057, 13061)
replace ags = 13074 if inlist(ags,13006, 13058)
replace ags = 13075 if inlist(ags,13001, 13059, 13062)
replace ags = 13076 if inlist(ags,13060, 13054)
* combine Göttingen
replace ags = 3159 if inlist(ags,3152, 3156)

collapse (mean) pct_empty_flats, by(ags)

tempfile pct_empty_flats
save `pct_empty_flats'

* LTE coverage
import excel "${data_raw}/urban_rural/LTE_Kreise_2019.xlsx", clear firstrow

ren (Kennziffer name LTE) (ags place_name lte_coverage)
keep ags place_name lte_coverage
destring ags, replace	
	
* combine Göttingen
replace ags = 3159 if inlist(ags,3152, 3156)
collapse (mean) lte_coverage, by(ags)	

* combine
merge 1:1 ags using `frac_1or2famhouse', nogen assert(3)
merge 1:1 ags using `living_space_pc', nogen assert(3)
merge 1:1 ags using `pct_empty_flats', nogen assert(3)

* also add population density 
merge 1:1 ags using "${data_raw}/urban_rural/pop_density.dta", nogen assert(3)

save "${data_derived}/urban_rural_structure.dta", replace
